package co.com.inventarios.persistencia;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.hibernate.Session;

import co.com.inventarios.modelo.RequiPro;
import co.com.inventarios.utils.HibernateSessionFactory;


public class QueryRequiProDao {

	private EntityManager getEntityManager() {
		return EntityManagerHelper.getEntityManager();
	}

	public List<RequiPro> Buscar_RequiPro(String empresa, String clase,
			String tipo, Double numero) {
		// EntityManagerHelper.log("finding RequiPro instance with property: "
		// + propertyName + ", value: " + value, Level.INFO, null);
		EntityManagerHelper.log("finding RequiPro instance with id: " + clase,
				Level.INFO, null);

		try {
			getEntityManager().clear();
			String queryString = "select model from RequiPro model where model.id.rqpempresa='"
					+ empresa
					+ "' AND model.id.rqpclase='"
					+ clase
					+ "' AND model.id.rqptipo='"
					+ tipo
					+ "' AND model.id.rqpnumero=" + numero + " ";

			Query query = getEntityManager().createQuery(queryString);

			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public Double MaxId(String empresa, String clase, String tipo, Double numero) {
		EntityManagerHelper.log("finding RequiPro instance with id: " + clase,
				Level.INFO, null);
		try {
			getEntityManager().clear();
			
			String queryString = "";

			queryString = "select max(model.id.rqpreg) from RequiPro model where model.id.rqpempresa='"
					+ empresa
					+ "' AND model.id.rqpclase='"
					+ clase
					+ "' AND model.id.rqptipo='"
					+ tipo
					+ "' AND model.id.rqpnumero=" + numero + " ";
			Query query = getEntityManager().createQuery(queryString);
			List list = query.getResultList();

			return (Double) list.get(0);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

//	public List<RequiPro> Buscar_RequiProTra(String empresa, String clase,
//			String tipo, String bodegas, String bodegae) {
//
//		EntityManagerHelper.log("finding RequiPro instance with id: " + clase,
//				Level.INFO, null);
//		RequiPro reg = new RequiPro();
//		try {
//
//			String queryString = "";
//			queryString = "select model from RequiPro model where model.requiDoc.bodega.bodcodigo='"
//					+ bodegas
//					+ "' and model.requiDoc.reqbodsale='"
//					+ bodegae
//					+ "'"
//					+ " AND ( model.label.lbcodnum = 1 OR model.label.lbcodnum = 6 ) AND  model.requiDoc.label.lbcodnum = 3";
//			Query query = getEntityManager().createQuery(queryString);
//
//			
//			if (query.getResultList().size() > 0) {
//				reg = (RequiPro) query.getResultList().get(0);
//				System.out.println("xxxxxxxxBumero de lineas: "
//						+ reg.getRqpcantEnt()
//						+ "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
//			}
//			
//			
//			return query.getResultList();
//
//		} catch (RuntimeException re) {
//			EntityManagerHelper.log("find by property name failed",
//					Level.SEVERE, re);
//			throw re;
//		}
//	}

	
	public String quitar_movtrasladoSeries(String empresa, String clase,
			String vinkey, String tipodoc, Double numedoc, int reg) {
		Session session = HibernateSessionFactory.getSessionFactory()
				.getCurrentSession();
		session.getTransaction().begin();
		Connection con = session.connection();
		String retorno = "";
		try {
			CallableStatement call = con
					.prepareCall("DELETE FROM MNGMCNSN WHERE MSNNUMEDOC = "
							+ numedoc + " AND MSNTIPODOC = '" + tipodoc
							+ "' AND MSNREG = " + reg + " ");

			call.execute();

			retorno = "";
			call.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		session.getTransaction().commit();
		return retorno;
	}


	 public List<RequiPro> Buscar_RequiProTra(String empresa, String clase,
	   String tipo, String bodegas, String bodegae) {
	  EntityManagerHelper.log("finding RequiPro instance with id: " + clase,
	    Level.INFO, null);
	  List<RequiPro> reg = new ArrayList<RequiPro>();
	  try {
	   getEntityManager().clear();
	   String queryString = "";
	   queryString = "select model from RequiPro model where model.requiDoc.bodega.bodcodigo='"
	     + bodegas
	     + "' and model.requiDoc.reqbodsale='"
	     + bodegae
	     + "'"
	     + " AND ( model.label.lbcodnum = 1 OR model.label.lbcodnum = 6 )  AND  model.requiDoc.label.lbcodnum = 3";
	   Query query = getEntityManager().createQuery(queryString);

	   reg = query.getResultList();
	   
//	   for(RequiPro obj : reg){
//		   System.out.println("Numero lineas -> "+obj.getRqpcantEnt()+"empresa:"+obj.getRequiDoc().getBodega().getBodcodigo()+"xxxxxx");
//	   }

	   return reg;
	  } catch (RuntimeException re) {
	   EntityManagerHelper.log("find by property name failed",
	     Level.SEVERE, re);
	   throw re;
	  }
	 }
	 

	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
